/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.wider.foundation.db;

import com.wider.foundation.xString;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.Map;
import org.w3c.dom.Element;

/**
 * Friendly class which abstract a DB connection
 * @author William Choi <avextk@gmail.com>
 */
class DBxConn {
    /***
     * Class Members
     */
    private static final Map<String, String>    cnstrs;
    private static Map<String, DBxConn>         cnnmap;


    /***
     * Class Initializer
     */
    static {
        cnstrs = new HashMap<String, String>();
        cnnmap = new HashMap<String, DBxConn>();
        cnstrs.put("mysql", "jdbc:mysql://##HOST/##DB");
        cnstrs.put("sqlserver", "jdbc:sqlserver://##HOST;databaseName=##DB");
    }

    /***
     * Static Methods
     */
    private static String makeConnectionString(Element conf) throws IOException{
        String cnnstr = "";
        String engine = conf.getAttribute("engine");
        String dbhost = conf.getAttribute("host");
        String dbname = conf.getAttribute("name");
        String dbauth = conf.getAttribute("authen");

        if (engine.equalsIgnoreCase("sqlserver") && (conf.getAttribute("instance").length() > 0)) {
            dbhost = dbhost + "/" + conf.getAttribute("instance");
        }

        cnnstr = cnstrs.get(engine);
        cnnstr = cnnstr.replace("##HOST", dbhost).replace("##DB", dbname);

        boolean winauth = dbauth.equalsIgnoreCase("sqlserver")
                            && dbauth.equalsIgnoreCase("win") ? true : false;

        //Load the driver [obsoleted since JDBC 4.0]
        //Class.forName(drvstr);

        if (winauth){
            cnnstr = cnnstr + ";integratedSecurity=true";
        }else{
            String dbuser = conf.getAttribute("user");
            if (dbuser.length() < 1){
                throw new IOException("User name for DB authentication does NOT exists in configuration.");
            }
            cnnstr = cnnstr.replace("://", "://" + dbuser + "@");
        }
        
        return cnnstr;
    }

    static DBxConn newDBxConn(String namespace, Element conf) throws IOException, ClassNotFoundException, SQLException{
        DBxConn newcnn = null;
        String  cnnstr = "";
       
        cnnstr = makeConnectionString(conf);
        newcnn = cnnmap.get(namespace + "::" + cnnstr);
        if (null == newcnn){
            String  dbpswd = conf.getAttribute("password");
            if (dbpswd.length() < 1) {
                throw new IOException("User password for DB authentication does NOT exists in configuration.");
            }
            newcnn = new DBxConn(namespace, cnnstr, dbpswd);
            cnnmap.put(namespace + "::" + cnnstr, newcnn);
            System.out.println("new DBxConnection is created.");
        }
        return newcnn;
    }

    /***
     * Instance Privates
     */
    private boolean     cnnctd;
    private String      nspace;
    private String      cnnurl;
    private Connection  dbconn;
    private Statement   qstmnt;

    /***
     * Constructors : make it private so that force the user to use the factory method to instanciate the DBxConn
     */
    private DBxConn(String namespace, String connection_string, String password) throws IOException, ClassNotFoundException, SQLException{
        cnnctd = false;
        cnnurl = connection_string;

        //Connect via the driver
        if (isWinAuth()){
            dbconn = DriverManager.getConnection(cnnurl);
        }else{
            String cnnstr;
            String dbuser = xString.substring(cnnurl, "://", "@");
            cnnstr = cnnurl.replace(dbuser, "://");
            dbuser = xString.substringBetween(dbuser, "://", "@");

            dbconn = DriverManager.getConnection(cnnstr, dbuser, password);
        }

        if (null == dbconn){
            throw new IOException("Unable to connect to " + cnnurl);
        }
        if (! dbconn.isValid(2000)){
            throw new IOException("The new JDBC connection to " + cnnurl + " is invalid.");
        }

        qstmnt = dbconn.createStatement();
        nspace = namespace;
        cnnctd = true;
    }

    /***
     * Destructors
     */
    @Override
     protected void finalize() throws Throwable{
        super.finalize();
        if (cnnctd){
            this.close();
        }
     }

    /***
     * Private Methods
     */
    private boolean isWinAuth(){
        boolean ret;
        String winauth = xString.substringAfter(cnnurl, "integratedSecurity=", 5).toLowerCase();
        ret = winauth.startsWith("true") | winauth.startsWith("sspi");
        return ret;
    }

    /***
     * Public Methods
     */
    void close() throws SQLException{
        dbconn.close();
        cnnctd = false;
        System.out.println("DBxConnection disconnected.");
    }

    String getNamespace(){
        return nspace;
    }

    String getConnectionString(){
        return cnnurl;
    }

    ResultSet query(DBxQuery xquery){
        String sql = xquery.statement
    }
/*
    public PreparedStatement newPreparedStatement() throws SQLException{
        dbconn.prepareStatement(cnnurl);
        
    }
 *
 */
}
